import os
import sys

# 将工程目录加入包扫描
currPath = os.path.abspath(os.path.dirname(__file__))
srcPath = os.path.split(currPath)[0]
rootPath = os.path.split(srcPath)[0]
projectPath = os.path.split(rootPath)[0]
sys.path.append(projectPath)

from typing import List
from src.tasks.enterprise.EntEnterprise import EntEnterprise
from src.utils import CommonUtils
from src.base import QeRankMapping


class SyncEnterpriseRatingData:
    def __init__(self, logger, localDb, dataCenterDb, fcdb):
        self.logger = logger
        self.localDb = localDb
        self.dataCenterDb = dataCenterDb
        self.fcdb = fcdb
        self.size = 1000
        self.abroadRatingOrgCode = ['80065840', '80058815', '80065841']
        self.abroadRatingOrg = ['惠誉国际信用评级有限公司', '标准普尔评估有限公司', '穆迪投资者服务公司']
        self.outerRatingOrgCode = ['80002154', '80002172', '80002192', '80062388', '80002101', '80000858', '80129270', '80117870', '80002099']
        self.outerRatingOrg = ['中诚信国际信用评级有限责任公司', '联合资信评估股份有限公司', '大公国际资信评估有限公司', '上海新世纪资信评估投资服务有限公司',
                               '上海远东资信评估有限公司', '中诚信证券评估有限公司', '联合信用评级有限公司', '东方金诚国际信用评估有限公司', '中证鹏元资信评估股份有限公司']

        self.exptRating = {'1': '正面', '2': "稳定", '3': "负面",
                           '4': "列入评级观察(可能调高)", '5': "列入评级观察(可能调低)", '6': "列入评级观察(走势不明)", '7': "待决",
                           '9': "无"}

    # 抽取CCX评级数据
    def __queryCcxRating__(self, t002Codes, otherCodes):
        sql1 = """
                SELECT t.credit_code, t.`level` AS `rank`, t.score, t.year FROM  
                (SELECT r.*, ROW_NUMBER() OVER ( PARTITION BY credit_code ORDER  BY YEAR desc ) AS NO  
                from base_repository_rating_result r
                INNER JOIN enterprise_info_custom  c ON c.credit_code = r.credit_code
                WHERE c.type_code <> 'T002' AND c.audit_status = 1 AND c.release_status = 1 AND c.ver = 0
                AND r.`level` is not NULL AND r.credit_code in %s
                )t 
                WHERE t.no =1 
        """
        sql2 = """
                SELECT t.credit_code,  t.platform_rank as `rank`, t.platform_rank_score  as score, t.year FROM  
                (SELECT r.*, ROW_NUMBER() OVER ( PARTITION BY r.credit_code ORDER  BY YEAR desc ) AS NO 
                from  base_repository_platform_result r
                INNER JOIN enterprise_info_custom  c ON c.credit_code = r.credit_code
                WHERE c.type_code ='T002' AND c.audit_status = 1 AND c.release_status = 1 AND c.ver = 0
                and r.ver=0 AND r.platform_rank is not null and r.credit_code in %s
                )t 
                WHERE t.no =1    
                """

        if len(otherCodes) > 0 and len(t002Codes) > 0:
            sql = "%s union all %s" % (sql1, sql2)
            param = (otherCodes, t002Codes)
        elif len(otherCodes) > 0:
            sql = sql1
            param = (otherCodes,)
        elif len(t002Codes) > 0:
            sql = sql2
            param = (t002Codes,)
        else:
            return []

        return self.dataCenterDb.Query(sql, param)

    # 从数据中心抽取QE评级
    def __queryQeRating__(self):
        sql = """
             SELECT credit_code, qe_level, CAST(effective AS UNSIGNED) as effective , `year`, rating_date FROM (
                SELECT  
                    *, ROW_NUMBER() OVER ( PARTITION BY credit_code ORDER BY rating_date DESC ) AS no
                FROM  qe_result_manage r
                WHERE status = 1 
            ) t WHERE t.no = 1
        """
        return self.dataCenterDb.Query(sql)

    def __queryOuterRating__(self, ratingOrgCodes: list):
        sql = """
            SELECT * FROM (
            SELECT COMPCODE,CREDITRATE,EXPTRATING,PUBLISHDATE,RATECOMNAME, 
            ROW_NUMBER() OVER (PARTITION by COMPCODE ORDER BY PUBLISHDATE DESC) AS no FROM TQ_BD_CREDITRTISSUE 
            WHERE RATECOMPCODE in %s
            AND RATETYPE in (1,34,44,66,85,86)
            ) t WHERE NO =1
         """
        return self.fcdb.Query(sql, (ratingOrgCodes,))

    def __updateQeRating__(self, enterpriseList):
        self.logger.info("加载 QE评级数据")
        qeRatingDatasAll = self.__queryQeRating__()
        qeRatingDatasAll = {d["credit_code"]: d for d in qeRatingDatasAll}
        self.logger.info("加载 QE评级数据：%d" % len(qeRatingDatasAll))

        for ent in enterpriseList:
            qeRatingData = None
            if ent.credit_code in qeRatingDatasAll.keys():
                qeRatingData = qeRatingDatasAll[ent.credit_code]
            ent.qe_eval = None if qeRatingData is None else qeRatingData["qe_level"]
            ent.qe_sort = None if qeRatingData is None else QeRankMapping.levelToValue(qeRatingData["qe_level"])
            # ent.qe_year = None if qeRatingData is None else qeRatingData["year"] # 不需要同步
            ent.qe_rating_date = None if qeRatingData is None else qeRatingData["rating_date"]
            ent.qe_state = None if qeRatingData is None else qeRatingData["effective"]

    def __updateOuterRating__(self, enterpriseList):
        self.logger.info("加载 外部评级数据")
        outerRatingDatas = self.__queryOuterRating__(self.outerRatingOrgCode)
        outerRatingDatas = {d["COMPCODE"]: d for d in outerRatingDatas}
        self.logger.info("加载 外部评级数据：%d " % len(outerRatingDatas))
        for ent in enterpriseList:
            if ent.comp_code in outerRatingDatas.keys():
                outerRatingData = outerRatingDatas[ent.comp_code]
                ent.external_eval = outerRatingData["CREDITRATE"]
                ent.external_eval_info = outerRatingData["EXPTRATING"]
                ent.external_eval_date = CommonUtils.strToDate(outerRatingData["PUBLISHDATE"])
                ent.external_eval_organization = outerRatingData["RATECOMNAME"]

    def __updateAroadRating__(self, enterpriseList):
        self.logger.info("加载 境外评级数据抽")
        outerRatingDatas = self.__queryOuterRating__(self.abroadRatingOrgCode)
        outerRatingDatas = {d["COMPCODE"]: d for d in outerRatingDatas}
        self.logger.info("加载 境外评级数据抽：%d " % len(outerRatingDatas))
        for ent in enterpriseList:
            if ent.comp_code in outerRatingDatas.keys():
                outerRatingData = outerRatingDatas[ent.comp_code]
                ent.foreign_external_eval = outerRatingData["CREDITRATE"]
                ent.foreign_external_eval_info = outerRatingData["EXPTRATING"]
                ent.foreign_external_eval_date = CommonUtils.strToDate(outerRatingData["PUBLISHDATE"])
                ent.foreign_external_eval_organization = outerRatingData["RATECOMNAME"]

    # 同步入口
    def update(self, enterpriseList: List[EntEnterprise]):
        self.logger.info("更新评级信息 开始")
        self.logger.info("待更新主体：%d" % len(enterpriseList))

        self.__updateQeRating__(enterpriseList)
        self.__updateOuterRating__(enterpriseList)
        self.__updateAroadRating__(enterpriseList)

        self.logger.info("更新评级信息 完成")
